<?php
/**
 * 考勤记录管理类
 * 处理考勤记录的增删改查操作
 */

class AttendanceManager {
    private $db;
    
    public function __construct($database) {
        $this->db = $database;
    }
    
    /**
     * 获取考勤记录列表
     */
    public function getAttendanceRecords($page = 1, $limit = 10, $filters = []) {
        $offset = ($page - 1) * $limit;
        
        $sql = "SELECT ar.*, u.username, u.full_name, u.department, u.position 
                FROM attendance_records ar 
                LEFT JOIN users u ON ar.user_id = u.id 
                WHERE 1=1";
        $params = [];
        
        // 应用过滤器
        if (!empty($filters['user_id'])) {
            $sql .= " AND ar.user_id = ?";
            $params[] = $filters['user_id'];
        }
        
        if (!empty($filters['start_date'])) {
            $sql .= " AND ar.record_date >= ?";
            $params[] = $filters['start_date'];
        }
        
        if (!empty($filters['end_date'])) {
            $sql .= " AND ar.record_date <= ?";
            $params[] = $filters['end_date'];
        }
        
        if (!empty($filters['status'])) {
            $sql .= " AND ar.status = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['search'])) {
            $sql .= " AND (u.username LIKE ? OR u.full_name LIKE ? OR u.department LIKE ?)";
            $searchTerm = "%{$filters['search']}%";
            $params = array_merge($params, array_fill(0, 3, $searchTerm));
        }
        
        $sql .= " ORDER BY ar.record_date DESC, ar.created_at DESC LIMIT ? OFFSET ?";
        $params[] = $limit;
        $params[] = $offset;
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $records = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // 获取总数
        $countSql = "SELECT COUNT(*) as total 
                    FROM attendance_records ar 
                    LEFT JOIN users u ON ar.user_id = u.id 
                    WHERE 1=1";
        
        if (!empty($filters['user_id'])) {
            $countSql .= " AND ar.user_id = ?";
        }
        
        if (!empty($filters['start_date'])) {
            $countSql .= " AND ar.record_date >= ?";
        }
        
        if (!empty($filters['end_date'])) {
            $countSql .= " AND ar.record_date <= ?";
        }
        
        if (!empty($filters['status'])) {
            $countSql .= " AND ar.status = ?";
        }
        
        if (!empty($filters['search'])) {
            $countSql .= " AND (u.username LIKE ? OR u.full_name LIKE ? OR u.department LIKE ?)";
        }
        
        $countStmt = $this->db->prepare($countSql);
        $countStmt->execute($params);
        $countResult = $countStmt->fetch(PDO::FETCH_ASSOC);
        $total = $countResult ? $countResult['total'] : 0;
        
        return [
            'records' => $records,
            'total' => $total,
            'page' => $page,
            'limit' => $limit,
            'total_pages' => ceil($total / $limit)
        ];
    }
    
    /**
     * 根据ID获取考勤记录
     */
    public function getAttendanceRecordById($recordId) {
        $sql = "SELECT ar.*, u.username, u.full_name, u.department, u.position 
                FROM attendance_records ar 
                LEFT JOIN users u ON ar.user_id = u.id 
                WHERE ar.id = ?";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$recordId]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    /**
     * 添加考勤记录
     */
    public function addAttendanceRecord($recordData) {
        // 检查数据库结构，兼容新旧版本
        $checkColumnSql = "SHOW COLUMNS FROM attendance_records LIKE 'total_work_hours'";
        $checkStmt = $this->db->query($checkColumnSql);
        $hasNewColumns = $checkStmt->rowCount() > 0;
        
        // 计算总工时
        $totalWorkHours = 0;
        $morningWorkHours = 0;
        $afternoonWorkHours = 0;
        $allDayWorkHours = 0;
        
        // 如果是出差状态，使用全天工时
        if ($recordData['status'] === 'business_trip') {
            $allDayWorkHours = $recordData['all_day_work_hours'] ?? 8.0;
            $totalWorkHours = $allDayWorkHours;
        } else {
            // 计算上午工时
            if (!empty($recordData['morning_clock_in']) && !empty($recordData['morning_clock_out'])) {
                $morningWorkHours = $this->calculateWorkHours($recordData['morning_clock_in'], $recordData['morning_clock_out']);
            }
            
            // 计算下午工时
            if (!empty($recordData['afternoon_clock_in']) && !empty($recordData['afternoon_clock_out'])) {
                $afternoonWorkHours = $this->calculateWorkHours($recordData['afternoon_clock_in'], $recordData['afternoon_clock_out']);
            }
            
            $totalWorkHours = $morningWorkHours + $afternoonWorkHours;
        }
        
        // 调试信息
        error_log("添加考勤记录 - 用户ID: {$recordData['user_id']}, 日期: {$recordData['record_date']}, 状态: {$recordData['status']}");
        error_log("数据库结构: " . ($hasNewColumns ? '新版本' : '旧版本'));
        error_log("总工时: {$totalWorkHours}");
        
        if ($hasNewColumns) {
            // 新版本数据库结构
            $sql = "INSERT INTO attendance_records (
                        user_id, record_date, 
                        morning_clock_in, morning_clock_out, morning_work_hours,
                        afternoon_clock_in, afternoon_clock_out, afternoon_work_hours,
                        all_day_work_hours, total_work_hours, status, notes
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            $params = [
                $recordData['user_id'],
                $recordData['record_date'],
                $recordData['morning_clock_in'] ?? null,
                $recordData['morning_clock_out'] ?? null,
                $morningWorkHours,
                $recordData['afternoon_clock_in'] ?? null,
                $recordData['afternoon_clock_out'] ?? null,
                $afternoonWorkHours,
                $allDayWorkHours,
                $totalWorkHours,
                $recordData['status'],
                $recordData['notes']
            ];
            
            error_log("新版本SQL: " . $sql);
            error_log("参数: " . implode(', ', $params));
            
            $stmt = $this->db->prepare($sql);
            $result = $stmt->execute($params);
            
            if ($result) {
                error_log("插入成功，影响行数: " . $stmt->rowCount());
            } else {
                $errorInfo = $stmt->errorInfo();
                error_log("插入失败: " . $errorInfo[2]);
            }
            
            return $result;
        } else {
            // 旧版本数据库结构 - 使用兼容模式
            // 对于旧版本，我们只使用上午的时间作为主要记录
            $sql = "INSERT INTO attendance_records (
                        user_id, record_date, 
                        clock_in, clock_out, work_hours, status, notes
                    ) VALUES (?, ?, ?, ?, ?, ?, ?)";
            
            $params = [
                $recordData['user_id'],
                $recordData['record_date'],
                $recordData['morning_clock_in'] ?? null,
                $recordData['morning_clock_out'] ?? null,
                $totalWorkHours,
                $recordData['status'],
                $recordData['notes']
            ];
            
            error_log("旧版本SQL: " . $sql);
            error_log("参数: " . implode(', ', $params));
            
            $stmt = $this->db->prepare($sql);
            $result = $stmt->execute($params);
            
            if ($result) {
                error_log("插入成功，影响行数: " . $stmt->rowCount());
            } else {
                $errorInfo = $stmt->errorInfo();
                error_log("插入失败: " . $errorInfo[2]);
            }
            
            return $result;
        }
    }
    
    /**
     * 更新考勤记录
     */
    public function updateAttendanceRecord($recordId, $recordData) {
        // 检查数据库结构，兼容新旧版本
        $checkColumnSql = "SHOW COLUMNS FROM attendance_records LIKE 'total_work_hours'";
        $checkStmt = $this->db->query($checkColumnSql);
        $hasNewColumns = $checkStmt->rowCount() > 0;
        
        // 计算总工时
        $totalWorkHours = 0;
        $morningWorkHours = 0;
        $afternoonWorkHours = 0;
        $allDayWorkHours = 0;
        
        // 如果是出差状态，使用全天工时
        if ($recordData['status'] === 'business_trip') {
            $allDayWorkHours = $recordData['all_day_work_hours'] ?? 8.0;
            $totalWorkHours = $allDayWorkHours;
        } else {
            // 计算上午工时
            if (!empty($recordData['morning_clock_in']) && !empty($recordData['morning_clock_out'])) {
                $morningWorkHours = $this->calculateWorkHours($recordData['morning_clock_in'], $recordData['morning_clock_out']);
            }
            
            // 计算下午工时
            if (!empty($recordData['afternoon_clock_in']) && !empty($recordData['afternoon_clock_out'])) {
                $afternoonWorkHours = $this->calculateWorkHours($recordData['afternoon_clock_in'], $recordData['afternoon_clock_out']);
            }
            
            $totalWorkHours = $morningWorkHours + $afternoonWorkHours;
        }
        
        if ($hasNewColumns) {
            // 新版本数据库结构
            $sql = "UPDATE attendance_records 
                    SET user_id = ?, record_date = ?, 
                        morning_clock_in = ?, morning_clock_out = ?, morning_work_hours = ?,
                        afternoon_clock_in = ?, afternoon_clock_out = ?, afternoon_work_hours = ?,
                        all_day_work_hours = ?, total_work_hours = ?, status = ?, notes = ?, 
                        updated_at = CURRENT_TIMESTAMP 
                    WHERE id = ?";
            
            $stmt = $this->db->prepare($sql);
            return $stmt->execute([
                $recordData['user_id'],
                $recordData['record_date'],
                $recordData['morning_clock_in'] ?? null,
                $recordData['morning_clock_out'] ?? null,
                $morningWorkHours,
                $recordData['afternoon_clock_in'] ?? null,
                $recordData['afternoon_clock_out'] ?? null,
                $afternoonWorkHours,
                $allDayWorkHours,
                $totalWorkHours,
                $recordData['status'],
                $recordData['notes'],
                $recordId
            ]);
        } else {
            // 旧版本数据库结构 - 使用兼容模式
            $sql = "UPDATE attendance_records 
                    SET user_id = ?, record_date = ?, 
                        clock_in = ?, clock_out = ?, work_hours = ?, status = ?, notes = ?, 
                        updated_at = CURRENT_TIMESTAMP 
                    WHERE id = ?";
            
            $stmt = $this->db->prepare($sql);
            return $stmt->execute([
                $recordData['user_id'],
                $recordData['record_date'],
                $recordData['morning_clock_in'] ?? null,
                $recordData['morning_clock_out'] ?? null,
                $totalWorkHours,
                $recordData['status'],
                $recordData['notes'],
                $recordId
            ]);
        }
    }
    
    /**
     * 删除考勤记录
     */
    public function deleteAttendanceRecord($recordId) {
        $sql = "DELETE FROM attendance_records WHERE id = ?";
        $stmt = $this->db->prepare($sql);
        return $stmt->execute([$recordId]);
    }
    
    /**
     * 计算工作时间
     */
    private function calculateWorkHours($clockIn, $clockOut) {
        if (empty($clockIn) || empty($clockOut)) {
            return 0;
        }
        
        $start = new DateTime($clockIn);
        $end = new DateTime($clockOut);
        $diff = $end->diff($start);
        
        // 转换为小时数
        $hours = $diff->h + ($diff->i / 60);
        return round($hours, 2);
    }
    
    /**
     * 获取考勤统计信息
     */
    public function getAttendanceStats($startDate = null, $endDate = null) {
        // 检查数据库结构，兼容新旧版本
        $checkColumnSql = "SHOW COLUMNS FROM attendance_records LIKE 'total_work_hours'";
        $checkStmt = $this->db->query($checkColumnSql);
        $hasNewColumns = $checkStmt->rowCount() > 0;
        
        if ($hasNewColumns) {
            // 新版本数据库结构
            $sql = "SELECT 
                    COUNT(*) as total_records,
                    SUM(CASE WHEN status = 'normal' THEN 1 ELSE 0 END) as normal_count,
                    SUM(CASE WHEN status = 'late' THEN 1 ELSE 0 END) as late_count,
                    SUM(CASE WHEN status = 'early_leave' THEN 1 ELSE 0 END) as early_leave_count,
                    SUM(CASE WHEN status = 'absent' THEN 1 ELSE 0 END) as absent_count,
                    SUM(CASE WHEN status = 'leave' THEN 1 ELSE 0 END) as leave_count,
                    SUM(CASE WHEN status = 'business_trip' THEN 1 ELSE 0 END) as business_trip_count,
                    SUM(CASE WHEN status = 'overtime' THEN 1 ELSE 0 END) as overtime_count,
                    AVG(total_work_hours) as avg_work_hours,
                    SUM(total_work_hours) as total_work_hours
                    FROM attendance_records";
        } else {
            // 旧版本数据库结构
            $sql = "SELECT 
                    COUNT(*) as total_records,
                    SUM(CASE WHEN status = 'normal' THEN 1 ELSE 0 END) as normal_count,
                    SUM(CASE WHEN status = 'late' THEN 1 ELSE 0 END) as late_count,
                    SUM(CASE WHEN status = 'early_leave' THEN 1 ELSE 0 END) as early_leave_count,
                    SUM(CASE WHEN status = 'absent' THEN 1 ELSE 0 END) as absent_count,
                    SUM(CASE WHEN status = 'leave' THEN 1 ELSE 0 END) as leave_count,
                    SUM(CASE WHEN status = 'business_trip' THEN 1 ELSE 0 END) as business_trip_count,
                    SUM(CASE WHEN status = 'overtime' THEN 1 ELSE 0 END) as overtime_count,
                    AVG(work_hours) as avg_work_hours,
                    SUM(work_hours) as total_work_hours
                    FROM attendance_records";
        }
        
        $params = [];
        
        if ($startDate && $endDate) {
            $sql .= " WHERE record_date BETWEEN ? AND ?";
            $params = [$startDate, $endDate];
        }
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        
        // 确保返回有效的数组，即使没有数据
        if (!$result) {
            return [
                'total_records' => 0,
                'normal_count' => 0,
                'late_count' => 0,
                'early_leave_count' => 0,
                'absent_count' => 0,
                'leave_count' => 0,
                'business_trip_count' => 0,
                'overtime_count' => 0,
                'avg_work_hours' => 0,
                'total_work_hours' => 0
            ];
        }
        
        return $result;
    }
    
    /**
     * 获取用户列表（用于下拉选择）
     */
    public function getUsersForSelect() {
        $sql = "SELECT id, username, full_name, department FROM users WHERE status = 1 ORDER BY full_name";
        $stmt = $this->db->query($sql);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * 检查某用户某天的考勤记录是否已存在
     */
    public function recordExists($userId, $recordDate, $excludeRecordId = null) {
        $sql = "SELECT COUNT(*) as count FROM attendance_records WHERE user_id = ? AND record_date = ?";
        $params = [$userId, $recordDate];
        
        if ($excludeRecordId) {
            $sql .= " AND id != ?";
            $params[] = $excludeRecordId;
        }
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        
        return $result['count'] > 0;
    }
    
    /**
     * 从Excel文件导入考勤记录
     */
    public function importFromExcel($filePath) {
        try {
            // 检查文件是否存在
            if (!file_exists($filePath)) {
                return ['success' => false, 'error' => '文件不存在'];
            }
            
            // 获取文件扩展名
            $extension = strtolower(pathinfo($filePath, PATHINFO_EXTENSION));
            
            // 调试信息
            error_log("文件路径: " . $filePath);
            error_log("文件扩展名: " . $extension);
            
            if ($extension === 'csv') {
                return $this->importFromCSV($filePath);
            } elseif ($extension === 'xlsx' || $extension === 'xls') {
                return $this->importFromXLSX($filePath);
            } else {
                // 尝试通过文件内容检测是否为CSV
                $fileContent = file_get_contents($filePath);
                if (strpos($fileContent, ',') !== false || strpos($fileContent, "\t") !== false) {
                    return $this->importFromCSV($filePath);
                }
                return ['success' => false, 'error' => '不支持的文件格式，仅支持CSV和Excel格式'];
            }
        } catch (Exception $e) {
            return ['success' => false, 'error' => '导入失败: ' . $e->getMessage()];
        }
    }
    
    /**
     * 从CSV文件导入
     */
    private function importFromCSV($filePath) {
        $imported = 0;
        $errors = [];
        
        // 添加调试信息
        error_log("开始导入CSV文件: " . $filePath);
        
        if (($handle = fopen($filePath, 'r')) !== FALSE) {
            $row = 0;
            
            while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) {
                $row++;
                
                // 跳过标题行
                if ($row === 1) {
                    error_log("跳过标题行: " . implode(',', $data));
                    continue;
                }
                
                // 检查数据列数
                if (count($data) < 3) {
                    $errorMsg = "第{$row}行数据不完整，只有 " . count($data) . " 列";
                    $errors[] = $errorMsg;
                    error_log($errorMsg);
                    continue;
                }
                
                // 解析数据
                $username = trim($data[0]);
                $recordDate = trim($data[1]);
                $status = trim($data[2]);
                $morningClockIn = isset($data[3]) ? trim($data[3]) : null;
                $morningClockOut = isset($data[4]) ? trim($data[4]) : null;
                $afternoonClockIn = isset($data[5]) ? trim($data[5]) : null;
                $afternoonClockOut = isset($data[6]) ? trim($data[6]) : null;
                $allDayWorkHours = isset($data[7]) ? floatval($data[7]) : null;
                $notes = isset($data[8]) ? trim($data[8]) : null;
                
                // 字符编码处理 - 确保中文字符正确编码
                $notes = $this->convertToUTF8($notes);
                
                // 调试信息
                error_log("解析第{$row}行数据: 用户名={$username}, 日期={$recordDate}, 状态={$status}");
                
                // 验证数据
                if (empty($username) || empty($recordDate) || empty($status)) {
                    $errorMsg = "第{$row}行缺少必要数据: 用户名='{$username}', 日期='{$recordDate}', 状态='{$status}'";
                    $errors[] = $errorMsg;
                    error_log($errorMsg);
                    continue;
                }
                
                // 获取用户ID
                $userId = $this->getUserIdByUsername($username);
                if (!$userId) {
                    $errorMsg = "第{$row}行用户不存在: {$username}";
                    $errors[] = $errorMsg;
                    error_log($errorMsg);
                    continue;
                }
                
                // 验证日期格式
                if (!preg_match('/^\d{4}-\d{1,2}-\d{1,2}$/', $recordDate)) {
                    $errorMsg = "第{$row}行日期格式错误: {$recordDate}";
                    $errors[] = $errorMsg;
                    error_log($errorMsg);
                    continue;
                }
                
                // 检查记录是否已存在
                if ($this->recordExists($userId, $recordDate)) {
                    $errorMsg = "第{$row}行记录已存在: {$username} - {$recordDate}";
                    $errors[] = $errorMsg;
                    error_log($errorMsg);
                    continue;
                }
                
                // 准备记录数据
                $recordData = [
                    'user_id' => $userId,
                    'record_date' => $recordDate,
                    'status' => $status,
                    'notes' => $notes
                ];
                
                // 根据状态设置时间字段
                if ($status === 'business_trip') {
                    $recordData['all_day_work_hours'] = $allDayWorkHours ?? 8.0;
                    error_log("出差状态，设置全天工时: " . $recordData['all_day_work_hours']);
                } else {
                    $recordData['morning_clock_in'] = $morningClockIn ?: null;
                    $recordData['morning_clock_out'] = $morningClockOut ?: null;
                    $recordData['afternoon_clock_in'] = $afternoonClockIn ?: null;
                    $recordData['afternoon_clock_out'] = $afternoonClockOut ?: null;
                    error_log("正常状态，设置时间: 上午上班={$morningClockIn}, 上午下班={$morningClockOut}, 下午上班={$afternoonClockIn}, 下午下班={$afternoonClockOut}");
                }
                
                // 添加记录
                if ($this->addAttendanceRecord($recordData)) {
                    $imported++;
                    error_log("第{$row}行导入成功");
                } else {
                    $errorMsg = "第{$row}行添加失败";
                    $errors[] = $errorMsg;
                    error_log($errorMsg);
                }
            }
            
            fclose($handle);
        } else {
            $errors[] = "无法打开文件: " . $filePath;
            error_log("无法打开CSV文件: " . $filePath);
        }
        
        $result = [
            'success' => true,
            'imported' => $imported,
            'errors' => $errors
        ];
        
        error_log("CSV导入完成: 成功导入 {$imported} 条记录，错误: " . count($errors));
        
        return $result;
    }
    
    /**
     * 将字符串转换为UTF-8编码
     */
    private function convertToUTF8($string) {
        if (empty($string)) {
            return $string;
        }
        
        // 检测当前编码
        $encoding = mb_detect_encoding($string, ['UTF-8', 'GBK', 'GB2312', 'BIG5', 'ASCII'], true);
        
        if ($encoding === false) {
            // 如果无法检测编码，尝试常见的中文编码
            if (function_exists('iconv')) {
                // 尝试从GBK转换
                $string = iconv('GBK', 'UTF-8//IGNORE', $string);
                if ($string === false) {
                    // 如果GBK转换失败，尝试GB2312
                    $string = iconv('GB2312', 'UTF-8//IGNORE', $string);
                }
            }
        } elseif ($encoding !== 'UTF-8') {
            // 如果不是UTF-8，转换为UTF-8
            if (function_exists('iconv')) {
                $string = iconv($encoding, 'UTF-8//IGNORE', $string);
            } elseif (function_exists('mb_convert_encoding')) {
                $string = mb_convert_encoding($string, 'UTF-8', $encoding);
            }
        }
        
        return $string;
    }
    
    /**
     * 从XLSX文件导入（简化版本，实际使用时需要安装PHPExcel或PhpSpreadsheet）
     */
    private function importFromXLSX($filePath) {
        // 这里简化处理，实际使用时需要安装PhpSpreadsheet库
        // 暂时返回错误提示
        return [
            'success' => false,
            'error' => 'Excel导入功能需要安装PhpSpreadsheet库。请使用CSV格式导入。'
        ];
    }
    
    /**
     * 根据用户名获取用户ID
     */
    public function getUserIdByUsername($username) {
        $sql = "SELECT id FROM users WHERE username = ? AND status = 1";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$username]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        
        return $result ? $result['id'] : null;
    }
}
?>
